UnitedHealth Group has a program called
Advocate4Me, which allows members to call an advocate and receive support for
their health care needs – whether that's behavioural, clinical, well-being,
health care financing, benefits, claims or pharmacy help.
Write a query to get the patients who made a call
within 7 days of their previous call. If a patient called more than twice in a
span of 7 days, count them as once.
table: callers
Solution:
with cte as
(
select *,lag(call_received,1) over (partition by policy_holder_id order by call_received) as next_call,
datediff(day, lag(call_received,1) over (partition by policy_holder_id order by call_received),call_received) as days
from callers
)
select count(distinct policy_holder_id) as patient_count from cte
where days <7
Output:
SQL Script:
GO
CREATE TABLE [dbo].[callers](
[policy_holder_id]
[int] NULL,
[case_id]
[varchar](50) NULL,
[call_category]
[varchar](50) NULL,
[call_received]
[datetime] NULL,
[call_duraton_secs]
[int] NULL,
[original_order]
[int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id],
[call_category],
[call_received],
[call_duraton_secs],
[original_order]) VALUES (50837000, N'dc63-acae-4f39-bb04', N'claims', CAST(N'2022-03-09T00:00:00.000' AS DateTime), 205, 130)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id],
[call_category],
[call_received],
[call_duraton_secs],
[original_order]) VALUES (50837000, N'41be-bebe-4bd0-a1ba', N'IT_support', CAST(N'2022-03-12T00:00:00.000' AS DateTime), 254, 129)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id],
[call_category],
[call_received],
[call_duraton_secs],
[original_order]) VALUES (50837000, N'bab1-3ec5-4867-90ae', N'benefits', CAST(N'2022-05-13T00:00:00.000' AS DateTime), 228, 339)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id],
[call_category],
[call_received],
[call_duraton_secs],
[original_order]) VALUES (50936674, N'12c8-b35c-48a3-b38d', N'claims', CAST(N'2022-05-31T00:00:00.000' AS DateTime), 240, 31)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id],
[call_category],
[call_received],
[call_duraton_secs],
[original_order]) VALUES (50886837, N'd0b4-8ea7-4b8c-aa8b', N'IT_support', CAST(N'2022-03-11T00:00:00.000' AS DateTime), 276, 16)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id],
[call_category],
[call_received],
[call_duraton_secs],
[original_order]) VALUES (50886837, N'a741-c279-41c0-90ba', N'', CAST(N'2022-03-19T00:00:00.000' AS DateTime), 131, 325)
GO